#!/usr/bin/env python
import sys
from lib.format_time import *
from lib.HandleMysql import *
from lib.switch_code import *
from shop_conn import *
from cate_city_tools import *
from lib.load_data import *
import json
import sys
import re
import os
sys.setdefaultencoding('utf-8')



def get_newest_shop():
    conn = set_stats_conn()
    query = "select ifnull(max(shop_id),0) from shop_info"
    items = conn.get_results(query)
    maxid = items[0][0]

    return maxid

#logic
def execute( param ):

    query = ""
    #print param
    conn = set_shop_conn()
    prod_conn = set_product_conn()
    stat_conn = set_stats_conn()
    stat_table = "shop_info"
    line_list = []
    #
    if(param == "0"):
        maxid = get_newest_shop()
	#maxid = 7500203426305
        query = "select id,userId,shopName,state,promote,banner,shoplogo,freeorpay,opentime,cateid,local,tel,cellphoneNumber from shop where id>%s "%maxid#date(opentime)='2012-11-13
    elif(param == "1"):
        query = "select id,userId,shopName,state,promote,banner,shoplogo,freeorpay,opentime,cateid,local,tel,cellphoneNumber from shop where id>0"
    else:
        query = "select id,userId,shopName,state,promote,banner,shoplogo,freeorpay,opentime,cateid,local,tel,cellphoneNumber from shop where id=%s"%param

    #print query
    items = conn.get_results(query)
    for item in items:
	shopId = item[0]
	userId = item[1]
	shopName = item[2]
	#shopName=shopName.encode("utf-8")
	state = item[3]
	intro = item[4]
	if intro == None:
	    intro = ''
	    
	banner = item[5]	    
	if banner != None:
	    banner = banner[2:34]
	else:
	    banner = ''
	    
	logo = item[6]
	if logo == None:
	    logo =''
	#print logo
	shop_type = item[7]
	open_time = item[8]
	cate_id = item[9]
	local = item[10]
	shopId = item[0]    
	userId = item[1]	
	shopName = item[2]
	#shopName=shopName.encode("utf-8")
	state = item[3]
	intro = item[4]
	if intro == None:
	    intro = ''
	    
	banner = item[5]	    
	if banner != None:
	    banner = banner[2:34]
	else:
	    banner = ''
	phone = item[11]
	mobile = item[12]
	#print cate_id
	if shopName != None:
	    shopName = shopName.replace("'","")
	    shopName = shopName.replace("\"","")
	    shopName = shopName.replace("\\"," ")
	if intro != None:
	    intro = intro.replace("'","")
	    intro = intro.replace("\"","")
	    intro = intro.replace("\\"," ")
	
	if open_time == None:
	    open_time = '0000-00-00'
	
	album_count=0
	sql_album = "select count(1) from album where shopid=%s"%shopId;
	items_album = conn.get_results(sql_album)
	try:
	    album_count = items_album[0][0]
	except Exception,e:
	    album_count=0
	    
	pic_count = 0
	sql_pic = "select count(1) from pic where state=0 and  shopid=%s"%shopId;
	items_pic = conn.get_results(sql_pic)
	try:
	    pic_count = items_pic[0][0]
	except Exception,e:
	    pic_count=0
	    
	memo_count = 0
	sql_memo = "select entityText from memo where entityid=%s and entitytype=100001 limit 1"%shopId;
	items_memo = conn.get_results(sql_memo)
	try:
	    if len(items_memo)>0:
		txt = items_memo[0][0]     
		pattern =  re.compile(r'jpg')
		matchs = pattern.findall(txt)
		memo_count = len(matchs)
	except Exception,e:
	    a=1
	# try:
	#     prod_count,prod_online_count = get_prod_count(shopId)
	# except Exception,e:
	#     a=1
	prod_count = 0
	prod_online_count =0
	prod_sale_count =0
	query1 = "select count(1) as prod_count,count(case when state=1 then 1 end),count(case when salestate=1 then 1 end) from t_product where shopid=%s"%shopId
	
	items1 = prod_conn.get_results(query1)
	if len(items1)>0:
	    prod_count = items1[0][0]
	    prod_online_count = items1[0][1]
	    prod_sale_count = items1[0][2]
	#cate
	#print cate_id
	dc = get_full_category(stat_conn,cate_id)    
	cat1 = 0
	cat1name = ''
	cat2 = 0
	cat2name=''
	if dc != None:
	    cat1 = dc[0][0]
	    cat1name = dc[0][1]
	    if len(dc) > 1:
		cat2 = dc[1][0]
		cat2name=dc[1][1]
	#print "test"
	#print local
	#local
	city_id = 0
	city_name = ''
	area_id = 0
	area_name = ''
	local_id= 0
	local_name = ''
	if local != None and local !='':
	    city = json.loads(local)[0]['L']
	    #print city
	    dc = get_full_citys(stat_conn,city)
	    if dc != None:
		city_id = dc[0][0]
		city_name = dc[0][1]
		if len(dc)>1:
		    area_id = dc[1][0]
		    area_name = dc[1][1]
		if len(dc)>2:
		    local_id= dc[2][0]
		    local_name = dc[2][1]
	#print    city_name ,area_name
	#sh_cnt = get_shop_count(shopId)
	#print open_time
	param1 = [shopId,userId,shopName,shop_type,cat1,cat1name,cat2,cat2name,city_id,city_name,area_id,area_name,local_id,local_name,intro,logo,banner,state,prod_count,prod_online_count,prod_sale_count,album_count,pic_count,memo_count,open_time,phone,mobile]
	line_list.append('\t'.join(map(lambda i : str(i), param1)))
    
    date = get_yesterday()
    cont = '\n'.join(line_list)
    path = "/work/opt/analytic/lh_tmp_data/shop_data/"
    file_name = "shop_info_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    #print file_path
    
    if param == "1":
	query = "truncate table %s;"%stat_table
	stat_conn.execute_sql(query)
    elif param != "0":
	query = "delete from %s where shop_id=%s;"%(stat_table,param)
	#print query
	stat_conn.execute_sql(query)
	
    load_data(stat_conn,path+file_name,stat_table,'\t')
    
    conn.close()
    prod_conn.close()
    stat_conn.close()
    
def get_comment(date):
    conn = set_shop_conn()
    stat_conn = set_stats_conn()
    stat_table = "comment"
    line_list = []
    
    q = "select id,shopid,`date` from comment where date(`date`)='%s'"%date
    items = conn.get_results(q)
    for item in items:
	cid = item[0]
	shopid = item[1]
	comm_date = item[2]
	
	param =[cid,shopid,comm_date]
	line_list.append('\t'.join(map(lambda i : str(i), param)))
    cont = '\n'.join(line_list)
    path = "/work3/opt/analytic/lh_tmp_data/shop_data/"
    file_name = "comment_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    load_data(stat_conn,path+file_name,stat_table,'\t')
    conn.close()
    stat_conn.close()
    
def get_order_log(date):
    conn = set_order_conn()
    stat_conn = set_stats_conn()
    stat_table = "order_log"
    line_list = []
    
    q = "select id,orderid,operatedate from t_orderlog where provincetype=3"#%date  and date(operatedate)='%s'
    items = conn.get_results(q)
    for item in items:
	log_id = item[0]
	order_id = item[1]
	operate_date = item[2]
	
	param =[log_id,order_id,operate_date]
	line_list.append('\t'.join(map(lambda i : str(i), param)))
    cont = '\n'.join(line_list)
    path = "/work3/opt/analytic/lh_tmp_data/shop_data/"
    file_name = "order_log_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    load_data(stat_conn,path+file_name,stat_table,'\t')
    conn.close()
    stat_conn.close()
    
def main():

   #date = '2012-05-28'
   #execute(date)
   param = 0;
   if len(sys.argv)==2:
        param=sys.argv[1]
        
   print "start"
   execute( param)
   
   date = get_yesterday()
   get_comment(date)
   
   get_order_log(date)
   print "end"


if __name__=='__main__':
    main()
    #date = get_yesterday()
    #path = "/work/opt/analytic/lh_tmp_data/shop_data/"
    #file_name = "shop_info_%s.txt"%date.replace('-',"")
    
    #print file_path
    #stat_conn = set_stats_conn()	
    #load_data(stat_conn,path+file_name,"shop_info_new",'\t')
